from flask import Flask,redirect,url_for,render_template,request
import mysql.connector
from flask import jsonify
app = Flask(__name__)

@app.route('/')
def hello_world():
    return render_template('e15.html')

@app.route('/llll/<detail>')
def hhh(detail):
    conn = None
    cur = None
    conn = mysql.connector.connect(
            host='localhost',
            user='root',  # 替换为你的MySQL用户名
             password='',  # 替换为你的MySQL密码
            database='thello'
        )
    cur = conn.cursor()
    if str(detail) =='all'or str(detail)=="m":
        sql="select name,func,role,time from recordP limit 6"
    else:
        sql="select name,func,role,time from recordP where uid='{}' order by time desc limit 6".format(detail)
    cur.execute(sql)
    my_name=[]
    my_func=[]
    my_role=[]
    time=[]

    result=cur.fetchone()
    while result:
        my_func.append(result[1])
        my_name.append(result[0])
        my_role.append(result[2])
        time.append(result[3])

        result=cur.fetchone()

    
    return{
        "message":"success",
        "my_name":my_name,
        "my_role":my_role,
        "my_func":my_func,
        "time":time
    }

    print(all_data)
    return all_data

@app.route('/lll')
def hh():
    detail="all"
    conn = None
    cur = None
    conn = mysql.connector.connect(
            host='localhost',
            user='root',  # 替换为你的MySQL用户名
             password='',  # 替换为你的MySQL密码
            database='thello'
        )
    cur = conn.cursor()
    if str(detail) =='all'or str(detail)=="m":
        sql="select name,func,role,time from recordP  limit 6"
    else:
        sql="select name,func,role,time from recordP where uid='{}'limit 6".format(detail)
    cur.execute(sql)
    my_name=[]
    my_func=[]
    my_role=[]
    time=[]
    result=cur.fetchone()
    while result:
        my_func.append(result[1])
        my_name.append(result[0])
        my_role.append(result[2])
        time.append(result[3])
        result=cur.fetchone()
    return{
        "message":"success",
        "my_name":my_name,
        "my_role":my_role,
        "my_func":my_func,
        "time":time
    }
@app.route('/jjj')
def jj():
    conn = None
    cur = None
    conn = mysql.connector.connect(
            host='localhost',
            user='root',  # 替换为你的MySQL用户名
             password='',  # 替换为你的MySQL密码
            database='thello'
        )
    cur = conn.cursor()
    sql="""SELECT
  CASE
    WHEN age >= 0 AND age < 20 THEN '0-20'
    WHEN age >= 20 AND age < 40 THEN '20-40'
    WHEN age >= 40 AND age < 60 THEN '40-60'
    WHEN age >= 60 AND age < 80 THEN '60-80'
    WHEN age >= 80 AND age <= 100 THEN '80-100'
    ELSE '其他'
  END AS age_group,
  COUNT(*) AS num_people
FROM
  personInfo
GROUP BY
  age_group;
"""
    cur.execute(sql)
    all_data = cur.fetchall()
    print(all_data)
    return all_data
@app.route('/ok')
def ok():
  conn = None
  cur = None
  conn = mysql.connector.connect(
              host='localhost',
              user='root',  # 替换为你的MySQL用户名
              password='',  # 替换为你的MySQL密码
              database='thello'
          )
  cur = conn.cursor()
  sql="""select place,COUNT(*) as count from personInfo GROUP BY place
  ;
  """
  cur.execute(sql)
  all_data = cur.fetchall()
  
  return all_data
@app.route('/hj')
def hj():
    conn = None
    cur = None
    conn = mysql.connector.connect(
                host='localhost',
                user='root',  # 替换为你的MySQL用户名
                password='',  # 替换为你的MySQL密码
                database='thello'
            )
    cur = conn.cursor()
    sql=""" select  utility_fee,COUNT(*) as count from residentsInfo2024 GROUP BY  utility_fee
    ;
    """
    cur.execute(sql)
    all_data = cur.fetchall()
    adata=[i for j,i in all_data]
    return adata
@app.route('/op')
def op():
    conn = None
    cur = None
    conn = mysql.connector.connect(
                host='localhost',
                user='root', 
                password='',  
                database='thello'
            )
    cur = conn.cursor()
    sql=""" select property_fee,COUNT(*) as count from residentsInfo2024 GROUP BY property_fee
    ;
    """
    cur.execute(sql)
    all_data = cur.fetchall()
    print(all_data)
    return all_data
@app.route('/kp')
def kp():
    conn = None
    cur = None
    conn = mysql.connector.connect(
                host='localhost',
                user='root',  # 替换为你的MySQL用户名
                password='',  # 替换为你的MySQL密码
                database='thello'
            )
    cur = conn.cursor()
    sql=""" select role,COUNT(*) as count from recordP GROUP BY role
    ;
    """
    cur.execute(sql)
    all_data = cur.fetchall()
    adata=[i for j,i in all_data]
    print(all_data)
    return adata
if __name__ == '__main__':
   app.run()